﻿using DataAccess.Exceptions;
using Model;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public static class HoraryDataAccess
    {
        public static string ConnString = Conexion.DataSource;

        public static Horary newHorary(Collection<HoraryDay> listHoraryDays, long codSubjectToTeacher, string gestion, string activity)
        {
            
            SqlConnection connection = null;
            try
            {
                if (verifyHoraryClassroom(listHoraryDays) == false)
                {
                    connection = new SqlConnection(ConnString);
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [Horario] (codDocenteMateria, horario, gestion, activo) VALUES (@V_codDocenteMateria, @V_horario, @V_gestion, @V_activo)";
                    cmd.Parameters.AddWithValue("V_codDocenteMateria", codSubjectToTeacher);
                    String horaryxml = Tools.XMLParser.ObjectToXml(listHoraryDays);
                    cmd.Parameters.AddWithValue("V_horario", horaryxml);
                    cmd.Parameters.AddWithValue("V_gestion", gestion);
                    cmd.Parameters.AddWithValue("@V_activo", activity);
                    connection.Open();

                    cmd.ExecuteNonQuery();                    
                    Horary newHorary = getHoraryActiveByGestionAndCodSubjectToTeacher(codSubjectToTeacher, gestion);
                    newHoraryClassroom(listHoraryDays, newHorary.code);
                    return (newHorary);
                }
                else
                {
                    throw new DataAccessException("Ya existe una materia asiganada a ese horario, verifique");
                }
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al crear un nuevo horario", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }                      
        }
        public static Horary modifyHorary(Collection<HoraryDay> listHoraryDays, long codSubjectToTeacher, string gestion, string activity)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE [Horario] SET horario=@V_horario, gestion=@V_gestion, activo=@V_activo WHERE codDocenteMateria=@V_codDocenteMateria";
                cmd.Parameters.AddWithValue("V_codDocenteMateria", codSubjectToTeacher);
                String horaryxml = Tools.XMLParser.ObjectToXml(listHoraryDays);
                cmd.Parameters.AddWithValue("V_horario", horaryxml);
                cmd.Parameters.AddWithValue("V_gestion", gestion);
                cmd.Parameters.AddWithValue("V_activo", activity);
                connection.Open();

                cmd.ExecuteNonQuery();
                Horary newHorary = getHoraryActiveByGestionAndCodSubjectToTeacher(codSubjectToTeacher, gestion);
                return (newHorary);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al modificar un nuevo horario", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static Horary getHoraryActiveByGestionAndCodSubjectToTeacher(long codSubjectToTeacher, string gestion)
        {
            SqlConnection connection = null;
            Horary result = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Horario] WHERE codDocenteMateria=@V_codDocenteMateria AND gestion=@V_gestion";
                cmd.Parameters.AddWithValue("V_codDocenteMateria", codSubjectToTeacher);                
                cmd.Parameters.AddWithValue("V_gestion", gestion);
                connection.Open();


                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = new Horary();
                    result.code = Convert.ToInt32(reader["codHorario"].ToString());

                    string xmlHorary = reader["horario"].ToString();
                    Collection<HoraryDay> horary = new Collection<HoraryDay>();
                    result.listHoraryDays = (Collection<HoraryDay>)Tools.XMLParser.XmlToObject(xmlHorary, horary.GetType());

                    result.codSubjectToTeacher = Convert.ToInt32(reader["codDocenteMateria"].ToString());
                    result.gestion = reader["gestion"].ToString();
                    result.activity = Convert.ToChar(reader["activo"].ToString());
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder al horario", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Horary getHoraryActiveByCode(string codTeacherToSubject)
        {
            SqlConnection connection = null;
            Horary result = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Horario] WHERE codDocenteMateria=@V_codDocenteMateria";
                cmd.Parameters.AddWithValue("V_codDocenteMateria", codTeacherToSubject);                
                connection.Open();


                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = new Horary();
                    result.code = Convert.ToInt32(reader["codHorario"].ToString());

                    string xmlHorary = reader["horario"].ToString();
                    Collection<HoraryDay> horary = new Collection<HoraryDay>();
                    result.listHoraryDays = (Collection<HoraryDay>)Tools.XMLParser.XmlToObject(xmlHorary, horary.GetType());

                    result.codSubjectToTeacher = Convert.ToInt32(reader["codDocenteMateria"].ToString());
                    result.gestion = reader["gestion"].ToString();
                    result.activity = Convert.ToChar(reader["activo"].ToString());
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder al horario", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Horary> getHoraryByGestion(string gestion)
        {
            SqlConnection connection = null;
            Collection<Horary> result = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Horario] WHERE gestion=@V_gestion";                
                cmd.Parameters.AddWithValue("V_gestion", gestion);
                connection.Open();


                SqlDataReader reader = cmd.ExecuteReader();
                result = new Collection<Horary>();
                while (reader.Read())
                {
                    Horary toAdd = new Horary();
                    toAdd.code = Convert.ToInt32(reader["codHorario"].ToString());

                    string xmlHorary = reader["horario"].ToString();
                    Collection<HoraryDay> horary = new Collection<HoraryDay>();
                    toAdd.listHoraryDays = (Collection<HoraryDay>)Tools.XMLParser.XmlToObject(xmlHorary, horary.GetType());

                    toAdd.codSubjectToTeacher = Convert.ToInt32(reader["codDocenteMateria"].ToString());
                    toAdd.gestion = reader["gestion"].ToString();
                    toAdd.activity = Convert.ToChar(reader["activo"].ToString());

                    result.Add(toAdd);
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al acceder al horario", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

        public static void newHoraryClassroom(Collection<HoraryDay> listHoraryDays, long codHorary)
        {
            for (int i = 0; i < listHoraryDays.Count; i++)
            {
                SqlConnection connection = null;
                try
                {
                    connection = new SqlConnection(ConnString);
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [HorarioAula] (codHorario, hora, codAula, dia, activo) VALUES(@V_codHorario, @V_hora, @V_codAula, @V_dia, 'A')";
                    HoraryDay horary = listHoraryDays[i];
                    cmd.Parameters.AddWithValue("V_codHorario", codHorary);
                    cmd.Parameters.AddWithValue("V_hora", (int)horary.hours);
                    cmd.Parameters.AddWithValue("V_codAula", horary.codCourse);
                    cmd.Parameters.AddWithValue("V_dia", (int)horary.days);
                    connection.Open();

                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error al acceder al horario aula", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }            
        }
        public static bool verifyHoraryClassroom(Collection<HoraryDay> listHoraryDays)
        {
            for (int i = 0; i < listHoraryDays.Count; i++)
            {
                SqlConnection connection = null;                
                try
                {
                    connection = new SqlConnection(ConnString);
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [HorarioAula] WHERE hora=@V_hora AND codAula=@V_codAula AND dia=@V_dia AND activo='A'";
                    HoraryDay horary = listHoraryDays[i];
                    cmd.Parameters.AddWithValue("V_hora", (int)horary.hours);
                    cmd.Parameters.AddWithValue("V_codAula", horary.codCourse);
                    cmd.Parameters.AddWithValue("V_dia", (int)horary.days);
                    connection.Open();


                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        return (true);
                    }                    
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error al acceder al horario aula", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return (false);
        }
    }
}
